package com.marvels.approve.loan.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.marvels.approve.consum.ApproveConsumerFinancialApi;
import com.marvels.approve.consum.ApproveConsumerLoanApplyApi;
import com.marvels.approve.consum.ApproveConsumerProductApi;
import com.marvels.approve.consum.ApproveConsumerSystemApi;
import com.marvels.approve.consum.ApproveConsumerUmApi;
import com.marvels.approve.loan.dao.LoanDetailDao;
import com.marvels.approve.loan.dto.CascadeChannelDto;
import com.marvels.approve.loan.dto.DjPersionInfoDto;
import com.marvels.approve.loan.dto.DownListConditionDto;
import com.marvels.approve.loan.dto.DownListCountAndSumDto;
import com.marvels.approve.loan.dto.DownListQueryDto;
import com.marvels.approve.loan.dto.FundSourceDTO;
import com.marvels.approve.loan.dto.LaAttachmentDto;
import com.marvels.approve.loan.dto.LcFarmPushInfoDto;
import com.marvels.approve.loan.dto.ListQueryConditionDto;
import com.marvels.approve.loan.dto.LoanDetailDto;
import com.marvels.approve.loan.dto.McamLoanApplyInfoDTO;
import com.marvels.approve.loan.dto.McptChannelDto;
import com.marvels.approve.loan.dto.McptProductType;
import com.marvels.approve.loan.dto.McsmSystemParam;
import com.marvels.approve.loan.dto.RealNameAuthentication;
import com.marvels.approve.loan.dto.YywBatchInfoDto;
import com.marvels.approve.loan.dto.YywPushInfoDto;
import com.marvels.approve.loan.service.LoanListService;
import com.marvels.common.constants.PageConstant;
import com.marvels.common.dto.BasicDataDTO;
import com.marvels.common.exception.MarvelsBusinessException;
import com.marvels.common.utils.CommonProperties;
import com.marvels.common.utils.MarvelsLogUtil;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 财务处理-放款处理-清单查询
 * 
 * @author tangpw
 *
 */
@Service
public class ListQueryImpl implements LoanListService {

	@Autowired
	private LoanDetailDao loanDetailDao;


	@Autowired
	private ApproveConsumerUmApi consumUmApi;
	
	@Autowired
	private ApproveConsumerLoanApplyApi consumerLoanApplyApi;

//	@Autowired
//	private CommonProperties configProperties;

	@Autowired
	private ApproveConsumerFinancialApi consumerFinancialApi;

	@Autowired
	private ApproveConsumerProductApi consumProductApi;

	@Autowired
	private ApproveConsumerSystemApi consumSystemApi;

	@Override
	public Page<LoanDetailDto> queryLoanList(ListQueryConditionDto listQueryConditionDto) throws MarvelsBusinessException{
		if (listQueryConditionDto.getPageSize() < 1) {
			listQueryConditionDto.setPageSize(PageConstant.PAGESIZE);
		}
		if (listQueryConditionDto.getPageNum() < 1) {
			listQueryConditionDto.setPageNum(PageConstant.CURRPAGE);
		}
		PageHelper.startPage(listQueryConditionDto.getPageNum(), listQueryConditionDto.getPageSize());
		Page<LoanDetailDto> dataList = loanDetailDao.queryLoanList(listQueryConditionDto);
		return dataList;
	}

	@Override
	public byte[] downLoadBatch(String fund, String ids, String fileBasePath, String sealBasePath) throws Exception {
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		String time = df.format(date);
		Map<String, Map<String, String>> sysParams = systemParamsDeal(new String[]{"PROTOCOL_DJZJY_NO1","PROTOCOL_LCNCZJY_NO2","PROTOCOL_YYWZJY_NO","PROTOCOL_MCZJY_NO"});
		// 打大包
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ZipOutputStream zipOut = new ZipOutputStream(baos);
		String[] idsData = null;
		if (StringUtils.isNotEmpty(ids)) {
			idsData = ids.substring(0, ids.length()).split(",");
		}
		if (StringUtils.isNotEmpty(fund)) {// 没有选择单独资金源下载(即下载所有资金源数据)
			if ("10".equals(fund)) {
				String djZipName = "大桔发标资料-" + time + ".zip";
				byte[] djZip = djFileDown((String) sysParams.get("PROTOCOL_DJZJY_NO1").get("param_value"), idsData,
						fileBasePath, sealBasePath);
				putZip(zipOut, djZipName, djZip);
			} else if ("12".equals(fund)) {
				String noneZipName = "理财农场发标资料-" + time + ".zip";
				byte[] noneZip = lcncFileDown((String) sysParams.get("PROTOCOL_LCNCZJY_NO2").get("param_value"),
						idsData, fileBasePath, sealBasePath);
				putZip(zipOut, noneZipName, noneZip);
			} else if ("15".equals(fund)) {
				String yywZipName = "宜盈网发标资料-" + time + ".zip";
				byte[] yywZip = yywFileDown((String) sysParams.get("PROTOCOL_YYWZJY_NO").get("param_value"), idsData,
						fileBasePath, sealBasePath);
				putZip(zipOut, yywZipName, yywZip);
			}

		} else {
			// PROTOCOL_DJZJY_NO1
			String djZipName = "大桔发标资料-" + time + ".zip";
			byte[] djZip = djFileDown((String) sysParams.get("PROTOCOL_DJZJY_NO1").get("param_value"), idsData, "", "");
			putZip(zipOut, djZipName, djZip);
			// PROTOCOL_LCNCZJY_NO2
			String noneZipName = "理财农场发标资料-" + time + ".zip";
			byte[] noneZip = lcncFileDown((String) sysParams.get("PROTOCOL_LCNCZJY_NO2").get("param_value"), idsData,
					"", "");
			putZip(zipOut, noneZipName, noneZip);
			// 宜盈网PROTOCOL_YYWZJY_NO
			String yywZipName = "宜盈网发标资料-" + time + ".zip";
			byte[] yywZip = yywFileDown((String) sysParams.get("PROTOCOL_YYWZJY_NO").get("param_value"), idsData, "",
					"");
			putZip(zipOut, yywZipName, yywZip);
			// 宜盈网PROTOCOL_YYWZJY_NO
			String mcZipName = "莫愁金服发标资料-" + time + ".zip";
			byte[] mcZip = mcFileDown((String) sysParams.get("PROTOCOL_MCZJY_NO").get("param_value"), idsData, "", "");
			putZip(zipOut, mcZipName, mcZip);
		}
		zipOut.flush();
		baos.flush();
		zipOut.close();
		byte[] batchByte = baos.toByteArray();
		baos.close();
		return batchByte;
	}

	/**
	 * 查询系统参数及处理
	 * @param values
	 * @return
	 */
	private Map<String, Map<String, String>> systemParamsDeal(String[] values) {
		Map<String, Map<String, String>> map = new HashMap<>();
		McsmSystemParam param=new McsmSystemParam();
		param.setParamNames(values);
		List<McsmSystemParam> paramsList = consumSystemApi.paramList(param);
		if (paramsList != null && paramsList.size() > 0) {
			for (McsmSystemParam mcsmSystemParam : paramsList) {
				String paramName = mcsmSystemParam.getParamName();
				String paramValue = mcsmSystemParam.getParamValue();
				String paramDesc = mcsmSystemParam.getParamDesc();
				if (!map.containsKey(paramName) || !map.get(paramName).containsKey("param_desc")) {
					Map<String, String> innerMap = new HashMap<>();
					innerMap.put("param_desc", paramDesc);
					innerMap.put("param_value", paramValue);
					map.put(paramName, innerMap);
				}
			}
		}
		return map;
	}

	/**
	 * 大桔
	 * 
	 * @param fundSource
	 * @param loanIds
	 * @param sealBasePath
	 * @return
	 * @throws IOException
	 */
	private byte[] djFileDown(String fundSource, String[] loanIds, String fileBasePath, String sealBasePath)
			throws IOException {
		String fileTypeName = "大桔网个人信息项目";
		//
		List<DjPersionInfoDto> list = loanDetailDao.djPersionInfo(fundSource, loanIds);
		int count = list.size();
		MarvelsLogUtil.getInstance().info(">>>>大桔网个人信息执行的结果条数COUNT！" + count);
		// 大桔网个人信息项目2条.xls
		String fileName = fileTypeName + count + "条.xls";
		byte[] excel = writeExcelBoForDj(list);

		// 大桔
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ZipOutputStream zipOut = new ZipOutputStream(baos);
		// 将大桔网个人信息项目2条.xls放进压缩包中
		zipOut.putNextEntry(new ZipEntry(fileName));
		zipOut.write(excel);
		zipOut.closeEntry();

		// 先查出所有贷款信息
		List<McamLoanApplyInfoDTO> loanInfo = loanDetailDao.lcFarmPersionInfo(loanIds, new String[] { fundSource });

		LaAttachmentDto dto=new LaAttachmentDto();
		dto.setFileIds(new String[] { fundSource });
		dto.setLoanIds(loanIds);
		List<LaAttachmentDto> attachList = consumerLoanApplyApi.compactAttach(dto);
		List<Map<String, Object>> djCompact = new ArrayList<Map<String, Object>>();
		if (loanInfo != null && loanInfo.size() > 0) {
			for (McamLoanApplyInfoDTO mcam : loanInfo) {
				Map<String, Object> dataMap = new HashMap<>();
				if (attachList != null && attachList.size() > 0) {
					for (LaAttachmentDto laAttachmentDto : attachList) {
						if (laAttachmentDto.getLoanId().equals(mcam.getLoanId())) {
							dataMap.put("loan_id", laAttachmentDto.getLoanId());
							dataMap.put("file_location", laAttachmentDto.getFileLocation());
							dataMap.put("applicant_name",mcam.getCustName());
							dataMap.put("applicant_id_nbr",mcam.getCustCardNo());
							dataMap.put("file_name", laAttachmentDto.getFileName());
						}
					}
					djCompact.add(dataMap);
				}
			}
		}
		MarvelsLogUtil.getInstance().info(">>>>>大桔合同的数量countCompact:" + djCompact.size());
		// 客户姓名=(资源URL=客户姓名)
		Map<String, Map<String, String>> compactMap = new HashMap<String, Map<String, String>>();
		for (int i = 0; i < djCompact.size(); i++) {
			Map info = (Map) djCompact.get(i);
			String loan_id = (String) info.get("loan_id");
			String file_location = (String) info.get("file_location");
			String applicant_name = (String) info.get("applicant_name");
			String applicantIdNbr = (String) info.get("applicant_id_nbr");
			String file_name = (String) info.get("file_name");

			String appName = applicant_name + applicantIdNbr;
			// 检查是否已经存在 客户姓名=(资源URL=客户姓名)
			Map<String, String> cMap = compactMap.get(appName);
			if (cMap == null) {
				cMap = new HashMap<String, String>();
			}
			cMap.put(sealBasePath + file_location, appName + "_" + file_name + i);
			compactMap.put(appName, cMap);
		}

		List<Map<String, Object>> pTypes = new ArrayList<>();
		List<McptProductType> productList = consumProductApi.mcptProductTypeInfo(new McptProductType());
		if (loanInfo != null && loanInfo.size() > 0) {
			for (McamLoanApplyInfoDTO mcam : loanInfo) {
				if (productList != null && productList.size() > 0) {
					for (McptProductType mcptProductType : productList) {
						Map<String, Object> innerMap = new HashMap<>();
						if (mcptProductType.getId().equals(mcam.getProductType())) {
							innerMap.put("pattern_type", mcptProductType.getPatternType());
							innerMap.put("applicantName", mcam.getCustName());
							innerMap.put("applicantIdNbr",mcam.getCustCardNo());
							innerMap.put("loan_id", mcam.getLoanId());
						}
						pTypes.add(innerMap);
					}
				}
			}
		}
		int ptCount = pTypes.size();
		for (int i = 0; i < ptCount; i++) {
			Map<String, Object> map = pTypes.get(i);
			String patternType = (String) map.get("pattern_type");
			String applicantName = (String) map.get("applicantName");
			String applicantIdNbr = (String) map.get("applicantIdNbr");
			String loanId = (String) map.get("loan_id");
			String appName = applicantName + applicantIdNbr;
			if (patternType.equals("B")) {
				List<Map<String, Object>> totalList = new ArrayList<>();
				LaAttachmentDto attachmentDto=new LaAttachmentDto();
				attachmentDto.setFileIds(new String[] { "0", "1", "2" });
				attachmentDto.setLoanIds(new String[] { loanId });
				attachmentDto.setAtmtTypes(new String[] { "A" });
				List<LaAttachmentDto> attList= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
				if (loanInfo != null && loanInfo.size() > 0) {
					for (McamLoanApplyInfoDTO mcam : loanInfo) {
						if (attList != null && attList.size() > 0) {
							for (LaAttachmentDto laAttachmentDto : attList) {
								Map<String, Object> innerMap = new HashMap<>();
								if (laAttachmentDto.getLoanId().equals(mcam.getLoanId() + "")) {
									innerMap.put("file_location", laAttachmentDto.getFileLocation());
									innerMap.put("applicant_name",mcam.getCustName() + "");
									innerMap.put("file_name", laAttachmentDto.getFileName());
								}
								totalList.add(innerMap);
							}
						}
					}
				}
				for (int a = 0; a < totalList.size(); a++) {
					Map info = (Map) totalList.get(a);
					String file_location = (String) info.get("file_location");
					applicantName = (String) info.get("applicant_name");
					String file_name = (String) info.get("file_name");
					// 检查是否已经存在 客户姓名=(资源URL=客户姓名)
					Map<String, String> cMap = compactMap.get(appName);
					if (cMap == null) {
						cMap = new HashMap<String, String>();
					}
					cMap.put(fileBasePath + file_location, appName + "_" + file_name + a);
					compactMap.put(appName, cMap);
				}
			} else {
				// 获取身份证路径
				RealNameAuthentication realName = consumUmApi.querByIdentiyCardNo(applicantIdNbr);
				if (realName != null) {
					// 检查是否已经存在 客户姓名=(资源URL=客户姓名)
					Map<String, String> cMap = compactMap.get(appName);
					if (cMap == null) {
						cMap = new HashMap<String, String>();
					}
					cMap.put(fileBasePath + realName.getIdcardimg1(), appName + "_身份证信息1");
					cMap.put(fileBasePath + realName.getIdcardimg2(), appName + "_身份证信息2");
					cMap.put(fileBasePath + realName.getPhoto(), appName + "_身份证信息3");
					compactMap.put(appName, cMap);
				}
			}
		}
		// 取出保单 征信
		LaAttachmentDto attachmentDto=new LaAttachmentDto();
		attachmentDto.setAtmtTypes(new String[] { "PDF_B", "B", "BAODAN" });
		List<LaAttachmentDto> attList= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
		List<Map<String, Object>> totalList = new ArrayList<>();
		if (loanInfo != null && loanInfo.size() > 0) { 
			for (McamLoanApplyInfoDTO mcam : loanInfo) {
				if (attList != null && attList.size() > 0) {
					for (LaAttachmentDto laAttachmentDto : attList) {
						Map<String, Object> innerMap = new HashMap<>();
						if (laAttachmentDto.getLoanId().equals(mcam.getLoanId())) {
							innerMap.put("file_location", laAttachmentDto.getFileLocation());
							innerMap.put("applicant_name",mcam.getCustName());
							innerMap.put("atmt_type", laAttachmentDto.getAtmtType());
							innerMap.put("applicant_id_nbr", mcam.getCustCardNo());
						}
						totalList.add(innerMap);
					}
				}
			}
		}
		for (int b = 0; b < totalList.size(); b++) {
			Map info = (Map) totalList.get(b);
			String file_location = (String) info.get("file_location");
			String atmt_type = (String) info.get("atmt_type");
			String applicantName = (String) info.get("applicant_name");
			String applicantIdNbr = (String) info.get("applicant_id_nbr");
			String appName = applicantName + applicantIdNbr;
			Map<String, String> cMap = compactMap.get(appName);
			if (cMap == null) {
				cMap = new HashMap<String, String>();
			}
			String tFileName = "";
			switch (atmt_type) {
			case "B":
				tFileName = "征信授权影像";
				break;
			case "PDF_B":
				tFileName = "征信报告";
				break;
			case "BAODAN":
				tFileName = "保单影像";
				break;
			default:
				break;
			}
			cMap.put(fileBasePath + file_location, appName + "_" + tFileName + b);
			compactMap.put(appName, cMap);
		}

		//
		byte[] buffer = new byte[1024];
		// 多文件压缩
		for (String appName : compactMap.keySet()) {
			// 压缩单个客户
			String dajuZipFile = appName + "_" + UUID.randomUUID().toString().substring(0, 8) + ".zip";
			ByteArrayOutputStream oneNameByte = new ByteArrayOutputStream();
			ZipOutputStream oneZipOut = new ZipOutputStream(oneNameByte);
			// (资源URL=客户姓名)
			Map<String, String> oneRes = compactMap.get(appName);
			// 多文件压缩
			int i = 0;
			for (String path : oneRes.keySet()) {
				// 压缩单个文件
				i++;
				File file = new File(path);
				if (!file.exists()) {
					continue;
				}
				// String name = oneRes.get(path);
				String name = file.getName();
				String fName = oneRes.get(path) + name.substring(name.lastIndexOf("."));
				if (file.exists()) {
					FileInputStream fis = null;
					try{
						fis = new FileInputStream(file);
						oneZipOut.putNextEntry(new ZipEntry(fName));
						int len = 0;
						// 读入需要下载的文件的内容，打包到zip文件
						while ((len = fis.read(buffer)) > 0) {
							oneZipOut.write(buffer, 0, len);
						}
						oneZipOut.closeEntry();
					}catch(Exception e){
						throw new IOException("读入需要下载的文件异常", e);
					}finally{
						if(fis != null){
							fis.close();
						}
					}
				}
			}
			oneZipOut.close();
			oneNameByte.flush();
			byte[] onePerson = oneNameByte.toByteArray();
			oneNameByte.close();
			// 将单个客户的压缩包压倒daju包
			if (onePerson.length > 0) {
				zipOut.putNextEntry(new ZipEntry(dajuZipFile));
				// 读入需要下载的文件的内容，打包到zip文件
				zipOut.write(onePerson);
				zipOut.closeEntry();
			}
		}

		zipOut.flush();
		zipOut.close();
		baos.flush();
		byte[] djByte = baos.toByteArray();
		baos.close();
		return djByte;
	}

	/**
	 * 理财农场
	 * 
	 * @param fundSource
	 * @param loanIds
	 * @param sealBasePath
	 * @return
	 * @throws IOException
	 */
	private byte[] lcncFileDown(String fundSource, String[] loanIds, String fileBasePath, String sealBasePath)
	throws IOException{
		String fileTypeName = "理财农场推送表";
		List<LcFarmPushInfoDto> batch = loanDetailDao.lcFarmPushInfo(fundSource, loanIds);
		int count = batch.size();
		MarvelsLogUtil.getInstance().info(">>>>>>>>>>理财农场推送表数据条数count:" + count);
		String fileName = fileTypeName + count + "条.xls";
		byte[] excel = writeExcelBoLCNC(batch);

		// 理财农场excel压缩
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ZipOutputStream zipOut = new ZipOutputStream(baos);
		zipOut.putNextEntry(new ZipEntry(fileName));
		zipOut.write(excel);
		zipOut.closeEntry();

		for (int i = 0; i < count; i++) {
			// 身份证
			List<String> urlList = new ArrayList<String>();
			// 取出保单 征信
			List<String> urlList1 = new ArrayList<String>();
			List<String> urlList2 = new ArrayList<String>();
			// 协议
			List<String> urlList3 = new ArrayList<String>();
			LcFarmPushInfoDto oneCLNC =batch.get(i);
			//查询亲属关系
			List<BasicDataDTO> list=(List<BasicDataDTO>) consumerLoanApplyApi.getRelationList();
			for(BasicDataDTO dto:list){
				if(oneCLNC.getRelationCode().equals(dto.getCode())){
					oneCLNC.setFamilyRelation(dto.getName());
				}
			}
			String loanId = oneCLNC.getLoanId();
			if (loanId == null || loanId.equals("")) {
				continue;
			}
			// 查询客户姓名
			String applicantName = "";
			String applicantIdNbr = "";
			String productType = "";
			List<McamLoanApplyInfoDTO> cmAppList = loanDetailDao.lcFarmPersionInfo(new String[] { loanId }, new String[] {});
			if (cmAppList.size() > 0) {
				McamLoanApplyInfoDTO info =  cmAppList.get(0);
				applicantName = info.getCustName();
				applicantIdNbr = info.getCustCardNo();
				productType =info.getProductType();
			}
			// 客户有名称才进入压缩文件
			if (StringUtils.isNotEmpty(applicantName)) {
				// 获取产品类型
				McptProductType maProductType = new McptProductType();
				maProductType.setProductType(productType);
				List<McptProductType> productList = consumProductApi.mcptProductTypeInfo(maProductType);
				if (productList != null && productList.size() > 0) {
					String type = productList.get(0).getPatternType();
					if (type.equals("B")) {
						// 身份证
						LaAttachmentDto attachmentDto=new LaAttachmentDto();
						attachmentDto.setAtmtTypes(new String[] { "A" });
						attachmentDto.setLoanIds(new String[] { loanId });
						attachmentDto.setFileIds( new String[] { "0", "1", "2" });
						List<LaAttachmentDto> attList= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
						if (attList != null && attList.size() > 0) {
							for (LaAttachmentDto laAttachmentDto : attList) {
								urlList.add(laAttachmentDto.getFileLocation());
							}
						}
					} else {
						// 获取身份证路径
						RealNameAuthentication realName = consumUmApi
								.querByIdentiyCardNo(applicantIdNbr);
						if (realName != null) {
							urlList.add(realName.getIdcardimg1());
							urlList.add(realName.getIdcardimg2());
							urlList.add(realName.getPhoto());
						}
					}
				}

				// 取出保单 征信
				LaAttachmentDto attachmentDto=new LaAttachmentDto();
				attachmentDto.setAtmtTypes(new String[] { "PDF_B", "B", "BAODAN" });
				attachmentDto.setLoanIds(new String[] { loanId });
				List<LaAttachmentDto> attList= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
				if (attList != null && attList.size() > 0) {
					for (LaAttachmentDto laAttachmentDto : attList) {
						String fileLocation = laAttachmentDto.getFileLocation();
						String atmtType = laAttachmentDto.getAtmtType();
						if (atmtType.equals("BAODAN")) {
							urlList1.add(fileLocation);
						}
						if (atmtType.equals("B") || atmtType.equals("PDF_B")) {
							urlList2.add(fileLocation);
						}
					}
				}
				// 取出 协议
				// List nongXieyiType =
				// loanDetailDao.lcFarmAchieveAgreement(fundSource, loanId);

				// 先查出所有贷款信息
				List<McamLoanApplyInfoDTO> loanInfo = loanDetailDao.lcFarmPersionInfo(loanIds,
						new String[] { fundSource });
				LaAttachmentDto dto=new LaAttachmentDto();
				dto.setLoanIds(loanIds);
				dto.setFileIds(new String[] { fundSource });
				List<LaAttachmentDto> attachList= consumerLoanApplyApi.compactAttach(dto);
				List<Map<String, Object>> nongXieyiType = new ArrayList<Map<String, Object>>();
				if (loanInfo != null && loanInfo.size() > 0) {
					for (McamLoanApplyInfoDTO mcam : loanInfo) {
						Map<String, Object> dataMap = new HashMap<>();
						if (attachList != null && attachList.size() > 0) {
							for (LaAttachmentDto laAttachmentDto : attachList) {
								if (laAttachmentDto.getLoanId().equals(mcam.getLoanId())) {
									dataMap.put("loan_id", laAttachmentDto.getLoanId());
									dataMap.put("file_location", laAttachmentDto.getFileLocation());
									dataMap.put("applicant_name", mcam.getCustName());
									dataMap.put("applicant_id_nbr",mcam.getCustCardNo());
									dataMap.put("file_name", laAttachmentDto.getFileName());
								}
							}
							nongXieyiType.add(dataMap);
						}
					}
				}
				for (int b = 0; b < nongXieyiType.size(); b++) {
					Map info = (Map) nongXieyiType.get(b);
					String file_location = (String) info.get("file_location");
					urlList3.add(file_location);
				}

				// 将该loanId打成包
				String nongZipFile = applicantName + applicantIdNbr + "_" + UUID.randomUUID().toString().substring(0, 8)
						+ ".zip";
				ByteArrayOutputStream nongByte = new ByteArrayOutputStream();
				ZipOutputStream nongZipOut = new ZipOutputStream(nongByte);
				byte[] buffer = new byte[1024];
				// 身份证
				for (int a = 0; a < urlList.size(); a++) {
					File file = new File(fileBasePath + urlList.get(a));
					String name = "身份证_" + a + ".png";
					if (file.exists()) {
						FileInputStream fis=null;
						try {
							fis = new FileInputStream(file);
							nongZipOut.putNextEntry(new ZipEntry(name));
							int len = 0;
							// 读入需要下载的文件的内容，打包到zip文件
							while ((len = fis.read(buffer)) > 0) {
								nongZipOut.write(buffer, 0, len);
							}
						} catch (Exception e) {
							MarvelsLogUtil.getInstance().error(">>>>>>>文件压缩异常");
						}finally {
							try {
								nongZipOut.closeEntry();
								fis.close();
							} catch (IOException e) {
								MarvelsLogUtil.getInstance().error(">>>>>>>流关闭异常");
							}
						}
					}
				}

				// 保单
				for (int a = 0; a < urlList1.size(); a++) {
					File file = new File(fileBasePath + urlList1.get(a));
					String name = "保单_" + a + ".png";
					if (file.exists()) {
						FileInputStream fis = null;
						try {
							fis=new FileInputStream(file);
							nongZipOut.putNextEntry(new ZipEntry(name));
							int len = 0;
							// 读入需要下载的文件的内容，打包到zip文件
							while ((len = fis.read(buffer)) > 0) {
								nongZipOut.write(buffer, 0, len);
							}
							nongZipOut.closeEntry();
							fis.close();
						} catch (Exception e) {
							MarvelsLogUtil.getInstance().error(">>>>>>>文件压缩异常");
						}finally {
							try {
								nongZipOut.closeEntry();
								fis.close();
							} catch (IOException e) {
								MarvelsLogUtil.getInstance().error(">>>>>>>流关闭异常");
							}
						}
					}
				}

				// 征信
				for (int a = 0; a < urlList2.size(); a++) {
					File file = new File(fileBasePath + urlList2.get(a));
					String name = "征信_" + a + ".png";
					if (file.exists()) {
						FileInputStream fis = null;
						try {
							fis=new FileInputStream(file);
							nongZipOut.putNextEntry(new ZipEntry(name));
							int len = 0;
							// 读入需要下载的文件的内容，打包到zip文件
							while ((len = fis.read(buffer)) > 0) {
								nongZipOut.write(buffer, 0, len);
							}
							nongZipOut.closeEntry();
							fis.close();
						} catch (Exception e) {
							MarvelsLogUtil.getInstance().error(">>>>>>>文件压缩异常");
						}finally {
							try {
								nongZipOut.closeEntry();
								fis.close();
							} catch (IOException e) {
								MarvelsLogUtil.getInstance().error(">>>>>>>流关闭异常");
							}
						}
					}
				}

				// 协议
				for (int a = 0; a < urlList3.size(); a++) {
					File file = new File(sealBasePath + urlList3.get(a));
					String name = "协议_" + a + ".pdf";
					if (file.exists()) {
						FileInputStream fis=null;
						try {
							fis = new FileInputStream(file);
							nongZipOut.putNextEntry(new ZipEntry(name));
							int len;
							// 读入需要下载的文件的内容，打包到zip文件
							while ((len = fis.read(buffer)) > 0) {
								nongZipOut.write(buffer, 0, len);
							}
							nongZipOut.closeEntry();
							fis.close();
						} catch (Exception e) {
							MarvelsLogUtil.getInstance().error(">>>>>>>文件压缩异常");
						}finally {
							try {
								nongZipOut.closeEntry();
								fis.close();
							} catch (IOException e) {
								MarvelsLogUtil.getInstance().error(">>>>>>>流关闭异常");
							}
						}
					}
				}
				nongZipOut.flush();
				nongZipOut.close();
				nongByte.flush();
				byte[] nongBt = nongByte.toByteArray();
				nongByte.close();

				zipOut.putNextEntry(new ZipEntry(nongZipFile));
				zipOut.write(nongBt);
				zipOut.closeEntry();
			}

		}
		// 理财农场 -- 结束
		zipOut.flush();
		zipOut.close();
		baos.flush();
		byte[] nongZip = baos.toByteArray();
		baos.close();
		return nongZip;
	}

	/**
	 * 宜盈网
	 */
	private byte[] yywFileDown(String fundSource, String[] loanIds, String fileBasePath, String sealBasePath)
			throws IOException {
		String fileTypeName = "宜盈网批量上标的清单";// -20170705-1418
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ZipOutputStream zipOut = new ZipOutputStream(baos);
		// 生成excel
		//List cexelList = loanDetailDao.yywPushInfo(fundSource, loanIds);
		
		//先查询所有用户信息
		List<YywPushInfoDto> cexelList = loanDetailDao.yywPushInfo(fundSource, loanIds);
		
		int size = cexelList.size();
		MarvelsLogUtil.getInstance().info("---yyw批量数据：" + size);
		// 打入包中
		if (cexelList.size() > 0) {
			byte[] excelByte = writeExcelBoForYYW(cexelList);
			String cexelName = fileTypeName + size + "条_v0.1(测试发标).xls";
			zipOut.putNextEntry(new ZipEntry(cexelName));
			zipOut.write(excelByte);
			zipOut.closeEntry();
		}
		for (int i = 0; i < size; i++) {
			ByteArrayOutputStream oneBaos = new ByteArrayOutputStream();
			ZipOutputStream oneZipOut = new ZipOutputStream(oneBaos);
			YywPushInfoDto dto = cexelList.get(i);
			String loanId = dto.getLoanId();
			String oneName = dto.getCustName() + dto.getCustName() + "_"
					+ UUID.randomUUID().toString().substring(0, 8) + ".zip";
			String pattern_type = dto.getPatternType() + "";
			String applicantIdNbr =dto.getCustCardNo();
			// 协议
			Map<String, Map<String, String>> sysParams = systemParamsDeal(new String[]{"ZIP_PROTOCOL_YYW"});
			// ZIP_PROTOCOL_YYW = "1,3"; 金融服务合同,四方协议
			String val = sysParams.get("ZIP_PROTOCOL_YYW").get("param_value");
			String doc = sysParams.get("ZIP_PROTOCOL_YYW").get("param_desc");
			if (sysParams.size() > 0) {
				String[] vals = val.split(",");
				String[] docs = doc.split(",");
				if (vals.length > 1) {
					// 金融服务合同
					String atmtTypeCon = "SEAL_PDF_" + vals[0];
					// 四方协议
					String atmtType4D = "SEAL_PDF_" + vals[1];
					String[] atmtTyp = { atmtType4D, atmtTypeCon };
					String[] loanIdAttach = { loanId };
					LaAttachmentDto attachmentDto=new LaAttachmentDto();
					attachmentDto.setAtmtTypes(atmtTyp);
					attachmentDto.setLoanIds(loanIdAttach);
					List<LaAttachmentDto> attList= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
					int index = 1;
					if (attList != null && attList.size() > 0) {
						for (LaAttachmentDto laAttachment : attList) {
							String atmtType = laAttachment.getAtmtType();
							String name = laAttachment.getFileName() + vals[0] + ".pdf";
							String fileLocation = laAttachment.getFileLocation();
							if (atmtTypeCon.equals(atmtType)) {// 金融服务合同
								if (docs.length > 0) {
									name = docs[0] + vals[0] + (index++) + ".pdf";
								}
							} else {// 四方协议
								if (docs.length > 1) {
									name = docs[1] + vals[1] + (index++) + ".pdf";
								}
							}
							file2Zip(sealBasePath, fileLocation, name, oneZipOut);
						}
					}
				}
			}
			//// 身份证 + 人脸识别
			if (pattern_type != null && pattern_type.equalsIgnoreCase("B")) {
				// 身份证 + 人脸识别
				LaAttachmentDto attachmentDto=new LaAttachmentDto();
				attachmentDto.setAtmtTypes(new String[] { "A", "R" });
				attachmentDto.setLoanIds(new String[] { loanId });
				List<LaAttachmentDto> sfs= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
				for (int a = 0; a < sfs.size(); a++) {
					LaAttachmentDto laAttachment = sfs.get(a);
					String file_location = laAttachment.getFileLocation();
					// applicantName =(String)info.get("applicant_name");
					File file = new File(fileBasePath + file_location);
					String name = "身份证_" + a + ".png";
					file2Zip(fileBasePath, file_location, name, oneZipOut);
				}
			} else {
				// 获取身份证路径
				RealNameAuthentication realName = consumUmApi.querByIdentiyCardNo(applicantIdNbr);
				if (realName != null) {
					String idcardimg1 = realName.getIdcardimg1();
					String idcardimg2 = realName.getIdcardimg2();
					String photo = realName.getPhoto();
					file2Zip(fileBasePath, idcardimg1, "身份证_0.png", oneZipOut);
					file2Zip(fileBasePath, idcardimg2, "身份证_1.png", oneZipOut);
					file2Zip(fileBasePath, photo, "身份证_2.png", oneZipOut);
				}
				// 人脸识别
				LaAttachmentDto attachmentDto=new LaAttachmentDto();
				attachmentDto.setAtmtTypes(new String[] { "R" });
				attachmentDto.setLoanIds(new String[] { loanId });
				List<LaAttachmentDto> sfs= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
				if(null != sfs && sfs.size()>0){
					for (int a = 0; a < sfs.size(); a++) {
						LaAttachmentDto info = sfs.get(a);
						String file_location = info.getFileLocation();
						// applicantName =(String)info.get("applicant_name");
						File file = new File(fileBasePath + file_location);
						String name = "renlian_" + a + ".png";
						file2Zip(fileBasePath, file_location, name, oneZipOut);
					}
				}
			}

			oneZipOut.flush();
			oneBaos.flush();
			oneZipOut.close();
			byte[] oneByte = oneBaos.toByteArray();
			oneBaos.close();
			zipOut.putNextEntry(new ZipEntry(oneName));
			zipOut.write(oneByte);
			zipOut.closeEntry();
		}

		zipOut.flush();
		baos.flush();
		zipOut.close();
		byte[] data = baos.toByteArray();
		baos.close();
		return data;
	}

	// 莫愁金服
	private byte[] mcFileDown(String fundSource, String[] loanIds, String fileBasePath, String sealBasePath)
			throws IOException {
		String fileTypeName = "莫愁批量上标的清单";// -20170705-1418
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ZipOutputStream zipOut = new ZipOutputStream(baos);
		// 生成excel
		List<YywBatchInfoDto> cexelList = loanDetailDao.yywBatchInfo(fundSource, loanIds);
		int size = cexelList.size();
		MarvelsLogUtil.getInstance().info(">>>>>>>>>>>yyw批量数据：" + size);
		// 打入包中
		if (cexelList.size() > 0) {
			byte[] excelByte = writeExcelBoForMC(cexelList);
			String cexelName = fileTypeName + size + "条_v0.1(测试发标).xls";
			zipOut.putNextEntry(new ZipEntry(cexelName));
			zipOut.write(excelByte);
			zipOut.closeEntry();
		}
		for (YywBatchInfoDto yywBatchInfoDto : cexelList) {
			ByteArrayOutputStream oneBaos = new ByteArrayOutputStream();
			ZipOutputStream oneZipOut = new ZipOutputStream(oneBaos);
			String loanId =yywBatchInfoDto.getLoanId();
			String oneName =yywBatchInfoDto.getCustName() +yywBatchInfoDto.getCustIdCard() + "_"
					+ UUID.randomUUID().toString().substring(0, 8) + ".zip";
			String custIdCard =yywBatchInfoDto.getCustIdCard();
			String pattern_type =yywBatchInfoDto.getPatternType() + "";
			// 协议
			Map<String, Map<String, String>> sysParams = systemParamsDeal(new String[]{"ZIP_PROTOCOL_MC"});
			// ZIP_PROTOCOL_YYW = "2,16,17,18"; 富爵个人金融服务协议、借款协议、莫愁金融咨询服务协议、授权书
			String val = sysParams.get("ZIP_PROTOCOL_MC").get("param_value");
			String doc = sysParams.get("ZIP_PROTOCOL_MC").get("param_desc");
			if (sysParams.size() > 0) {
				String[] vals = val.split(",");
				String[] docs = doc.split(",");
				if (vals.length > 1) {
					// 富爵个人金融服务协议
					String atmtType1 = "SEAL_PDF_" + vals[0];
					// 借款协议
					String atmtType2 = "SEAL_PDF_" + vals[1];
					// 莫愁金融咨询服务协议
					String atmtType3 = "SEAL_PDF_" + vals[2];
					// 授权书
					String atmtType4 = "SEAL_PDF_" + vals[3];
					LaAttachmentDto attachmentDto=new LaAttachmentDto();
					attachmentDto.setAtmtTypes(new String[] { atmtType1, atmtType2 });
					attachmentDto.setLoanIds(new String[] { loanId });
					List<LaAttachmentDto> attList= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
					int index = 1;
					if (attList != null && attList.size() > 0) {
						for (LaAttachmentDto laAttachment : attList) {
							String atmtType = laAttachment.getAtmtType();
							String name = laAttachment.getFileName() + vals[0] + ".pdf";
							String fileLocation = laAttachment.getFileLocation();
							if (atmtType1.equals(atmtType)) { // 富爵个人金融服务协议
								if (docs.length > 0) {
									name = docs[0] + vals[0] + (index++) + ".pdf";
								}
							} else if (atmtType2.equals(atmtType)) {// 借款协议
								if (docs.length > 1) {
									name = docs[1] + vals[1] + (index++) + ".pdf";
								}
							} else if (atmtType3.equals(atmtType)) {// 莫愁金融咨询服务协议
								if (docs.length > 1) {
									name = docs[2] + vals[2] + (index++) + ".pdf";
								}
							} else {
								if (docs.length > 1) {// 授权书
									name = docs[3] + vals[3] + (index++) + ".pdf";
								}
							}
							file2Zip(sealBasePath, fileLocation, name, oneZipOut);
						}
					}
				}
			}

			//// 身份证 + 人脸识别
			if (pattern_type != null && pattern_type.equalsIgnoreCase("B")) {
				// 身份证 + 人脸识别
				LaAttachmentDto attachmentDto=new LaAttachmentDto();
				attachmentDto.setAtmtTypes(new String[] { "A", "R" });
				attachmentDto.setLoanIds(new String[] { loanId });
				List<LaAttachmentDto> sfs= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
				for (int a = 0; a < sfs.size(); a++) {
					LaAttachmentDto info = sfs.get(a);
					String file_location = info.getFileLocation();
					String name = "身份证_" + a + ".png";
					file2Zip(fileBasePath, file_location, name, oneZipOut);
				}
			} else {
				// 获取身份证路径
				RealNameAuthentication realName = consumUmApi.querByIdentiyCardNo(custIdCard);
				if (realName != null) {
					String idcardimg1 = realName.getIdcardimg1();
					String idcardimg2 = realName.getIdcardimg2();
					String photo = realName.getPhoto();
					file2Zip(fileBasePath, idcardimg1, "身份证_0.png", oneZipOut);
					file2Zip(fileBasePath, idcardimg2, "身份证_1.png", oneZipOut);
					file2Zip(fileBasePath, photo, "身份证_2.png", oneZipOut);
				}
				// 人脸识别
				LaAttachmentDto attachmentDto=new LaAttachmentDto();
				attachmentDto.setAtmtTypes(new String[] { "R" });
				attachmentDto.setLoanIds(new String[] { loanId });
				List<LaAttachmentDto> sfs= consumerLoanApplyApi.achieveAttachInfo(attachmentDto);
				for (int a = 0; a < sfs.size(); a++) {
					LaAttachmentDto info = sfs.get(a);
					String file_location = info.getFileLocation();
					// applicantName =(String)info.get("applicant_name");
					String name = "renlian_" + a + ".png";
					file2Zip(fileBasePath, file_location, name, oneZipOut);
				}
			}

			oneZipOut.flush();
			oneBaos.flush();
			oneZipOut.close();
			byte[] oneByte = oneBaos.toByteArray();
			oneBaos.close();
			zipOut.putNextEntry(new ZipEntry(oneName));
			zipOut.write(oneByte);
			zipOut.closeEntry();

		}

		zipOut.flush();
		baos.flush();
		zipOut.close();
		byte[] data = baos.toByteArray();
		baos.close();
		return data;

	}

	private void putZip(ZipOutputStream zipOut, String name, byte[] data) throws IOException {
		if (data.length > 0) {
			zipOut.putNextEntry(new ZipEntry(name));
			zipOut.write(data);
			zipOut.closeEntry();
		}
	}

	/**
	 * 生成大桔资金Excel
	 * 
	 * @return
	 * @throws IOException
	 */
	private byte[] writeExcelBoForDj(List<DjPersionInfoDto> dto) {
		jxl.write.WritableWorkbook wwb;
		try {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			wwb = Workbook.createWorkbook(baos);
			jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 30);
			writeExcelBoForDjHeader(ws);

			for (int i = 0; i < dto.size(); i++) {
				DjPersionInfoDto info = dto.get(i);
				String pjType = info.getPjType();
				String amt = info.getAmt();
				String terms = info.getTerms();
				String pjDep =info.getPjDep();
				String NAME = info.getCustName();
				String idNbr = info.getIdNbr();
				String sex =info.getSex();
				String age = info.getAge();
				String eduLevel =info.getEduLevelName();
				String marryStatus =info.getMarryStatus();
				// Integer amt = (Integer)info.get("amt");
				// Integer terms = (Integer)info.get("terms");
				String chCount = info.getChCount();
				String achCity = info.getAchCity();
				String revenue =info.getRevenue();
				String reveSource = info.getReveSource();
				String housePro =info.getHousePro();
				String carPro = info.getCarPro();
				String otherPro =info.getOtherPro();
				String useCard = info.getUseCard();
				String useRate = info.getUseRate();
				String companyCity = info.getCompanyCity();
				String workTime =info.getWorkTime();
				String companyIndustry =info.getCompanyIndustry();
				String position =info.getPositton();
				String companyPro = info.getCompanyPro();
				String receiveNumber = info.getReceiveNumber();
				String bankName =info.getBankName();
				// String bankProvince = (String)info.get("bankProvince");
				String bankCity = info.getBankCity();
				String receiveBpoint = info.getReceiveBpoint();
				String receiveNo = info.getReceiveNumber();
				String receiveName =info.getReceiveName();
				String openBName = info.getBankName();
				String openBAddr = info.getOpenBAddr();
				String openBPoint = info.getReceiveBpoint();

				if (StringUtils.isNotEmpty(pjType))
					ws.addCell(new jxl.write.Label(0, i + 3, pjType));
				ws.addCell(new jxl.write.Label(1, i + 3,amt));
				if (StringUtils.isNotEmpty(terms)) {
					ws.addCell(new jxl.write.Label(2, i + 3, terms));
				}
				if (StringUtils.isNotEmpty(pjDep))
					ws.addCell(new jxl.write.Label(3, i + 3,pjDep));
				if (StringUtils.isNotEmpty(NAME))
					ws.addCell(new jxl.write.Label(4, i + 3, NAME));
				if (StringUtils.isNotEmpty(idNbr))
					ws.addCell(new jxl.write.Label(5, i + 3,idNbr));
				if (StringUtils.isNotEmpty(sex))
					ws.addCell(new jxl.write.Label(6, i + 3,sex));
				if (StringUtils.isNotEmpty(age))
					ws.addCell(new jxl.write.Label(7, i + 3,age));
				// 教育级别
				ws.addCell(new jxl.write.Label(8, i + 3, eduLevel));
				if (StringUtils.isNotEmpty(marryStatus))
					ws.addCell(new jxl.write.Label(9, i + 3,marryStatus));
				if (StringUtils.isNotEmpty(chCount))
					ws.addCell(new jxl.write.Label(10, i + 3,chCount));
				if (StringUtils.isNotEmpty(achCity))
					ws.addCell(new jxl.write.Label(11, i + 3,achCity));
				if (StringUtils.isNotEmpty(revenue))
					ws.addCell(new jxl.write.Label(12, i + 3, revenue));
				if (StringUtils.isNotEmpty(reveSource))
					ws.addCell(new jxl.write.Label(13, i + 3,reveSource));
				if (StringUtils.isNotEmpty(housePro))
					ws.addCell(new jxl.write.Label(14, i + 3,housePro));
				if (StringUtils.isNotEmpty(carPro))
					ws.addCell(new jxl.write.Label(15, i + 3, carPro));
				if (StringUtils.isNotEmpty(otherPro))
					ws.addCell(new jxl.write.Label(16, i + 3,otherPro));
				if (StringUtils.isNotEmpty(useCard))
					ws.addCell(new jxl.write.Label(17, i + 3,useCard));
				if (StringUtils.isNotEmpty(useRate))
					ws.addCell(new jxl.write.Label(18, i + 3, useRate));
				if (StringUtils.isNotEmpty(companyCity))
					ws.addCell(new jxl.write.Label(19, i + 3,companyCity));
				if (StringUtils.isNotEmpty(workTime))
					ws.addCell(new jxl.write.Label(20, i + 3,workTime));
				if (StringUtils.isNotEmpty(companyIndustry))
					ws.addCell(new jxl.write.Label(21, i + 3, companyIndustry));
				if (StringUtils.isNotEmpty(position))
					ws.addCell(new jxl.write.Label(22, i + 3,position));
				if (StringUtils.isNotEmpty(companyPro))
					ws.addCell(new jxl.write.Label(23, i + 3, companyPro));
				// 替换成公司数据
				ws.addCell(new jxl.write.Label(24, i + 3, receiveNo == null ? "" : receiveNo));
				ws.addCell(new jxl.write.Label(25, i + 3, receiveName == null ? "" : receiveName));
				ws.addCell(new jxl.write.Label(26, i + 3, openBName == null ? "" : openBName));
				ws.addCell(new jxl.write.Label(27, i + 3, openBAddr == null ? "" : openBAddr));
				ws.addCell(new jxl.write.Label(28, i + 3, openBPoint == null ? "" : openBPoint));
			}
			wwb.write();
			// 关闭Excel工作薄对象
			wwb.close();
			baos.flush();
			byte[] excel = baos.toByteArray();
			baos.close();
			return excel;
		} catch (Exception e) {
			MarvelsLogUtil.getInstance().error(">>>>>>>生成大桔资金Excel错误:" + e);
		}
		return null;
	}

	private void writeExcelBoForDjHeader(jxl.write.WritableSheet ws) throws Exception {
		ws.mergeCells(0, 0, 30, 0);
		Label label = new Label(0, 0, "导入项目:个人借款的项目，未发布状态,类型：DEBX，等额本息（期限为月），DQHB，到期还本（期限为天），XXHB，先息后本（期限为月）");
		ws.addCell(label);
		//
		ws.mergeCells(0, 1, 3, 1);
		Label lab01 = new Label(0, 1, "项目信息");
		ws.addCell(lab01);

		ws.mergeCells(4, 1, 11, 1);
		Label lab02 = new Label(4, 1, "个人信息");
		ws.addCell(lab02);

		ws.mergeCells(12, 1, 18, 1);
		Label lab03 = new Label(12, 1, "个人资产及征信信息");
		ws.addCell(lab03);

		ws.mergeCells(19, 1, 23, 1);
		Label lab04 = new Label(19, 1, "企业信息");
		ws.addCell(lab04);

		ws.mergeCells(24, 1, 28, 1);
		Label lab05 = new Label(24, 1, "个人银行卡信息");
		ws.addCell(lab05);
		ws.addCell(new jxl.write.Label(29, 1, "数据源（不要修改或者删除）"));
		//
		ws.addCell(new jxl.write.Label(0, 2, "类型"));
		ws.addCell(new jxl.write.Label(1, 2, "借款金额（元）"));
		ws.addCell(new jxl.write.Label(2, 2, "期限数"));
		ws.addCell(new jxl.write.Label(3, 2, "项目描述"));
		ws.addCell(new jxl.write.Label(4, 2, "借款人姓名"));
		ws.addCell(new jxl.write.Label(5, 2, "身份证号码 "));
		ws.addCell(new jxl.write.Label(6, 2, "性别"));
		ws.addCell(new jxl.write.Label(7, 2, "年龄"));
		ws.addCell(new jxl.write.Label(8, 2, "学历"));
		ws.addCell(new jxl.write.Label(9, 2, "婚姻状况"));
		ws.addCell(new jxl.write.Label(10, 2, "子女情况"));
		ws.addCell(new jxl.write.Label(11, 2, "户籍城市"));
		ws.addCell(new jxl.write.Label(12, 2, "收入情况"));
		ws.addCell(new jxl.write.Label(13, 2, "收入来源"));
		ws.addCell(new jxl.write.Label(14, 2, "房产"));
		ws.addCell(new jxl.write.Label(15, 2, "车产"));
		ws.addCell(new jxl.write.Label(16, 2, "其它信用贷款"));
		ws.addCell(new jxl.write.Label(17, 2, "未销户信用卡"));
		ws.addCell(new jxl.write.Label(18, 2, "信用卡额度使用率"));
		ws.addCell(new jxl.write.Label(19, 2, "企业所在地"));
		ws.addCell(new jxl.write.Label(20, 2, "现公司工作时间"));
		ws.addCell(new jxl.write.Label(21, 2, "公司行业"));
		ws.addCell(new jxl.write.Label(22, 2, "岗位"));
		ws.addCell(new jxl.write.Label(23, 2, "公司性质"));
		ws.addCell(new jxl.write.Label(24, 2, "收款卡号"));// 收款卡号 收款户名 开户行 开户行地区
														// 开户网点
		ws.addCell(new jxl.write.Label(25, 2, "收款户名"));
		ws.addCell(new jxl.write.Label(26, 2, "开户行"));
		ws.addCell(new jxl.write.Label(27, 2, "开户行地区"));
		ws.addCell(new jxl.write.Label(28, 2, "开户网点"));
		ws.addCell(new jxl.write.Label(29, 2, "否"));
	}

	/**
	 * 生成理财农场Excel
	 * 
	 * @return
	 * @throws IOException
	 */
	public byte[] writeExcelBoLCNC(List<LcFarmPushInfoDto> dto) {
		jxl.write.WritableWorkbook wwb;
		try {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			wwb = Workbook.createWorkbook(baos);
			jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 28);
			ws.addCell(new jxl.write.Label(0, 0, "进件日期"));
			ws.addCell(new jxl.write.Label(1, 0, "合同编号"));
			ws.addCell(new jxl.write.Label(2, 0, "借款人姓名"));
			ws.addCell(new jxl.write.Label(3, 0, "身份证号码"));
			ws.addCell(new jxl.write.Label(4, 0, "电话号码"));
			ws.addCell(new jxl.write.Label(5, 0, "住址"));
			ws.addCell(new jxl.write.Label(6, 0, "借款金额"));
			ws.addCell(new jxl.write.Label(7, 0, "分期期数"));
			ws.addCell(new jxl.write.Label(8, 0, "还款方式D:一次性还款；R:先息后本；H:等额本金;M:等额本息"));
			ws.addCell(new jxl.write.Label(9, 0, "借款账号"));
			ws.addCell(new jxl.write.Label(10, 0, "还款账号"));
			ws.addCell(new jxl.write.Label(11, 0, "借款用途"));
			ws.addCell(new jxl.write.Label(12, 0, "是否个人抵押贷（1：是   2：否）"));
			ws.addCell(new jxl.write.Label(13, 0, "性别"));
			ws.addCell(new jxl.write.Label(14, 0, "出生年月"));
			ws.addCell(new jxl.write.Label(15, 0,
					"最高学历(00=博士10=研究生20=大学本科30=大专40=中专/中等技校50=技术学校60=高中70=初中80=小学90=文盲或文盲99=未知)"));
			ws.addCell(new jxl.write.Label(16, 0, "婚姻状况10=未婚20=已婚21=已婚且有子女22=再婚23=复婚30=丧偶40=离婚"));
			ws.addCell(new jxl.write.Label(17, 0, "有无子女Y-有N-无"));
			ws.addCell(new jxl.write.Label(18, 0, "有无房产Y-有N-无 "));
			ws.addCell(new jxl.write.Label(19, 0, "有无车Y-有N-无"));
			ws.addCell(new jxl.write.Label(20, 0, "有无房贷Y-有N-无"));
			ws.addCell(new jxl.write.Label(21, 0, "有无车贷Y-有N-无"));
			ws.addCell(new jxl.write.Label(22, 0, "单位名称"));
			ws.addCell(new jxl.write.Label(23, 0, "公司性质1=大型国有企业、上市公司单位2=事业单位3=公务员单位4=其它"));
			ws.addCell(new jxl.write.Label(24, 0, "任职部门"));
			ws.addCell(new jxl.write.Label(25, 0, "现工作年限"));
			ws.addCell(new jxl.write.Label(26, 0, "公司规模0=50人以下1=50-100人2=100-500人3=500人以上"));
			ws.addCell(new jxl.write.Label(27, 0, "公司地址"));
			ws.addCell(new jxl.write.Label(28, 0, "职务0=初级领导1=高级领导2=中级领导3=一般员工4=其他9=未知"));
			ws.addCell(new jxl.write.Label(29, 0, "年收入0=5万以下1=5万——10万2=10万——15万3=15万——20万4=20万——30万5=30万以上"));
			ws.addCell(new jxl.write.Label(30, 0, "公司电话"));
			ws.addCell(new jxl.write.Label(31, 0, "公司邮箱"));
			ws.addCell(new jxl.write.Label(32, 0, "直系亲属联系人姓名"));
			ws.addCell(new jxl.write.Label(33, 0, "直系亲属联系人关系"));
			ws.addCell(new jxl.write.Label(34, 0, "直系亲属联系人手机"));
			ws.addCell(new jxl.write.Label(35, 0, "直系亲属联系人住宅电话"));
			ws.addCell(new jxl.write.Label(36, 0, "直系亲属联系人工作单位"));
			ws.addCell(new jxl.write.Label(37, 0, "直系亲属联系人年收入0=5万以下1=5万——10万2=10万——15万3=15万——20万4=20万——30万5=30万以上"));
			ws.addCell(new jxl.write.Label(38, 0, "其他联系人姓名"));
			ws.addCell(new jxl.write.Label(39, 0, "其他联系人关系"));
			ws.addCell(new jxl.write.Label(40, 0, "其他联系人手机号码"));
			ws.addCell(new jxl.write.Label(41, 0, "其他联系人住宅电话"));
			ws.addCell(new jxl.write.Label(42, 0, "其他联系人工作单位"));
			ws.addCell(new jxl.write.Label(43, 0, "其他联系人年收入0=5万以下1=5万——10万2=10万——15万3=15万——20万4=20万——30万5=30万以上"));

			for (int i = 0; i < dto.size(); i++) {
				LcFarmPushInfoDto info = dto.get(i);
				String createTime = info.getCreateTime();
				String NAME = info.getCustName();
				String infoId = info.getCompactCode();
				String idNbr =info.getIdNbr();
				String cellphnone_nbr =info.getCellphone();
				String cur_home_address =info.getAddr();
				String amt = info.getAmt();
				String terms =info.getLoanTerms();
				String repayment_method =info.getPayMethod();
				String receive_act_number = info.getReceiveNum();
				String payment_act_number =info.getPayNum();
				String purpose = info.getPurpose();
				String b =info.getIsDiya();
				String sex = info.getSex();
				String applicant_birth_date = info.getAcBirthday();
				String eduLevel = info.getEduLevel();
				String marryStatus =info.getMarryStatus();
				String zinv = info.getHasChild();
				String fangchan =info.getHasHouse();
				String carPro =info.getHasCar();
				String housePro = info.getHasHouseloan();
				String cardai =info.getHasCarloan();
				String company_name =info.getConpanyName();
				String companyIndustry =info.getConpanyOwnership();
				String kaifa =info.getConpanyDept();
				String workTime = info.getWorkTime();
				String companyCity =info.getConpanySize();
				String companyAddr = info.getCompanyAddr();
				String POSITION = info.getCompanyPosition();
				String nsr =info.getCompanyRevenue();
				String company_tel =info.getCompanyTel();
				String company_email =info.getCompanyMail();
				String familyName = info.getFamilyName();
				String familyRelation =info.getFamilyRelation();
				String familyCellphone = info.getFamilyCellphone();
				String familyTel =info.getFamilyTel();
				String familyWorkaddr = info.getFamilyWorkaddr();
				String familyRevenue =info.getFamilyRevenue();
				String otherName = info.getOtherName();
				String otherRel =info.getOtherRel();
				String otherCellphone = info.getOtherCellphone();
				String otherTel = info.getOtherTel();
				String otherWorkaddr =info.getOtherWorkaddr();
				String otherRevenue = info.getOtherRevenue();

				if (createTime != null)
					ws.addCell(new jxl.write.Label(0, i + 1, "" + createTime));
				if (infoId != null)
					ws.addCell(new jxl.write.Label(1, i + 1, "" + infoId));
				if (NAME != null)
					ws.addCell(new jxl.write.Label(2, i + 1,NAME));
				if (idNbr != null)
					ws.addCell(new jxl.write.Label(3, i + 1,idNbr));
				if (cellphnone_nbr != null)
					ws.addCell(new jxl.write.Label(4, i + 1,cellphnone_nbr));
				if (cur_home_address != null)
					ws.addCell(new jxl.write.Label(5, i + 1,cur_home_address));
				ws.addCell(new jxl.write.Label(6, i + 1, amt+""));
				if (terms != null)
					ws.addCell(new jxl.write.Label(7, i + 1,terms));
				if (repayment_method != null)
					ws.addCell(new jxl.write.Label(8, i + 1, repayment_method));
				if (receive_act_number != null)
					ws.addCell(new jxl.write.Label(9, i + 1,receive_act_number));
				if (payment_act_number != null)
					ws.addCell(new jxl.write.Label(10, i + 1,payment_act_number));
				if (purpose != null)
					ws.addCell(new jxl.write.Label(11, i + 1,purpose));
				if (b != null)
					ws.addCell(new jxl.write.Label(12, i + 1,b));
				if (sex != null)
					ws.addCell(new jxl.write.Label(13, i + 1,sex));
				if (applicant_birth_date != null)
					ws.addCell(new jxl.write.Label(14, i + 1,applicant_birth_date));
				if (eduLevel != null)
					ws.addCell(new jxl.write.Label(15, i + 1, eduLevel));
				if (marryStatus != null)
					ws.addCell(new jxl.write.Label(16, i + 1, marryStatus));
				if (zinv != null)
					ws.addCell(new jxl.write.Label(17, i + 1,zinv));
				if (fangchan != null)
					ws.addCell(new jxl.write.Label(18, i + 1, fangchan));
				if (carPro != null)
					ws.addCell(new jxl.write.Label(19, i + 1,carPro));
				if (housePro != null)
					ws.addCell(new jxl.write.Label(20, i + 1, housePro));
				if (cardai != null)
					ws.addCell(new jxl.write.Label(21, i + 1,cardai));
				if (company_name != null)
					ws.addCell(new jxl.write.Label(22, i + 1, company_name));
				if (companyIndustry != null)
					ws.addCell(new jxl.write.Label(23, i + 1,companyIndustry));
				if (kaifa != null)
					ws.addCell(new jxl.write.Label(24, i + 1, kaifa));
				if (workTime != null)
					ws.addCell(new jxl.write.Label(25, i + 1,workTime));
				if (companyCity != null)
					ws.addCell(new jxl.write.Label(26, i + 1,companyCity));
				if (companyAddr != null)
					ws.addCell(new jxl.write.Label(27, i + 1, companyAddr));
				if (POSITION != null)
					ws.addCell(new jxl.write.Label(28, i + 1, POSITION));
				if (nsr != null)
					ws.addCell(new jxl.write.Label(29, i + 1, nsr));
				if (company_tel != null)
					ws.addCell(new jxl.write.Label(30, i + 1, company_tel));
				if (company_email != null)
					ws.addCell(new jxl.write.Label(31, i + 1,company_email));
				if (familyName != null)
					ws.addCell(new jxl.write.Label(32, i + 1,familyName));
				if (familyRelation != null)
					ws.addCell(new jxl.write.Label(33, i + 1, familyRelation));
				if (familyCellphone != null)
					ws.addCell(new jxl.write.Label(34, i + 1,familyCellphone));
				if (familyTel != null)
					ws.addCell(new jxl.write.Label(35, i + 1,familyTel));
				if (familyWorkaddr != null)
					ws.addCell(new jxl.write.Label(36, i + 1, familyWorkaddr));
				if (familyRevenue != null)
					ws.addCell(new jxl.write.Label(37, i + 1, familyRevenue));
				if (StringUtils.isNotEmpty(otherName))
					ws.addCell(new jxl.write.Label(38, i + 1,otherName));
				if (StringUtils.isNotEmpty(otherRel))
					ws.addCell(new jxl.write.Label(39, i + 1,otherRel));
				if (StringUtils.isNotEmpty(otherCellphone))
					ws.addCell(new jxl.write.Label(40, i + 1,otherCellphone));
				if (StringUtils.isNotEmpty(otherTel))
					ws.addCell(new jxl.write.Label(41, i + 1,otherTel));
				if (StringUtils.isNotEmpty(otherWorkaddr))
					ws.addCell(new jxl.write.Label(42, i + 1, otherWorkaddr));
				if (StringUtils.isNotEmpty(otherRevenue))
					ws.addCell(new jxl.write.Label(43, i + 1,otherRevenue));

			}
			wwb.write();
			// 关闭Excel工作薄对象
			wwb.close();
			baos.flush();
			byte[] excel = baos.toByteArray();
			baos.close();
			return excel;
		} catch (Exception e) {
			MarvelsLogUtil.getInstance().error(">>>>>>>理财农场推送表excel生成失败:" + e);
		}
		return null;
	}

	// 将文件打入到zip包
	private void file2Zip(String basePath, String filePath, String fileName, ZipOutputStream oneZipOut) {
		FileInputStream fis =null;
		try {
			File file = new File(basePath + filePath);
			if (file.exists()) {
				fis = new FileInputStream(file);
				oneZipOut.putNextEntry(new ZipEntry(fileName));
				int len = 0;
				byte[] buffer = new byte[1024];
				// 读入需要下载的文件的内容，打包到zip文件
				while ((len = fis.read(buffer)) > 0) {
					oneZipOut.write(buffer, 0, len);
				}
			}
		} catch (IOException e) {
			MarvelsLogUtil.getInstance().error(">>>>>>>压缩文件异常:" + e);
		}finally {
			try {
				fis.close();
				oneZipOut.closeEntry();
			} catch (Exception e2) {
				MarvelsLogUtil.getInstance().error(">>>>>>>流关闭异常:" + e2);
			}
		}
	}

	private byte[] writeExcelBoForYYW(List<YywPushInfoDto> dtoList) {
		jxl.write.WritableWorkbook wwb;
		try {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			wwb = Workbook.createWorkbook(baos);
			jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 34);
			ws.addCell(new jxl.write.Label(0, 0, "项目类型"));
			ws.addCell(new jxl.write.Label(1, 0, "借款金额(元)"));
			ws.addCell(new jxl.write.Label(2, 0, "期限单位"));
			ws.addCell(new jxl.write.Label(3, 0, "期限"));
			ws.addCell(new jxl.write.Label(4, 0, "项目性质"));
			ws.addCell(new jxl.write.Label(5, 0, "项目描述"));
			ws.addCell(new jxl.write.Label(6, 0, "借款人姓名"));
			ws.addCell(new jxl.write.Label(7, 0, "身份证号"));
			ws.addCell(new jxl.write.Label(8, 0, "性别"));
			ws.addCell(new jxl.write.Label(9, 0, "年龄"));
			ws.addCell(new jxl.write.Label(10, 0, "学历"));
			ws.addCell(new jxl.write.Label(11, 0, "婚姻"));
			ws.addCell(new jxl.write.Label(12, 0, "子女状况"));
			ws.addCell(new jxl.write.Label(13, 0, "户籍城市"));
			ws.addCell(new jxl.write.Label(14, 0, "未销户信用卡"));
			ws.addCell(new jxl.write.Label(15, 0, "信用卡使用情况"));
			ws.addCell(new jxl.write.Label(16, 0, "月收入"));
			ws.addCell(new jxl.write.Label(17, 0, "车产"));
			ws.addCell(new jxl.write.Label(18, 0, "房产"));
			ws.addCell(new jxl.write.Label(19, 0, "其他信用贷款"));
			ws.addCell(new jxl.write.Label(20, 0, "企业所在地"));
			ws.addCell(new jxl.write.Label(21, 0, "现公司工作年限"));
			ws.addCell(new jxl.write.Label(22, 0, "公司行业"));
			ws.addCell(new jxl.write.Label(23, 0, "公司性质"));
			ws.addCell(new jxl.write.Label(24, 0, "岗位"));
			ws.addCell(new jxl.write.Label(25, 0, "账户类型"));
			ws.addCell(new jxl.write.Label(26, 0, "收款卡号"));
			ws.addCell(new jxl.write.Label(27, 0, "开户行"));
			ws.addCell(new jxl.write.Label(28, 0, "开户行地区"));
			ws.addCell(new jxl.write.Label(29, 0, "开户网点"));
			ws.addCell(new jxl.write.Label(30, 0, "邀请"));
			ws.addCell(new jxl.write.Label(31, 0, "出让人姓名"));
			ws.addCell(new jxl.write.Label(32, 0, "出让人手机"));
			ws.addCell(new jxl.write.Label(33, 0, "出让人身份证"));

			java.util.Random random = new java.util.Random();// 定义随机类
			for (int i = 0; i < dtoList.size(); i++) {
				YywPushInfoDto dto = dtoList.get(i);
				ws.addCell(new jxl.write.Label(0, i + 1, "DEBX"));
				String data = dto.getPrincipalAmtAct();
				ws.addCell(new jxl.write.Label(1, i + 1, getVal(data)));
				ws.addCell(new jxl.write.Label(2, i + 1, "M"));
				data = dto.getApplyTerms();
				ws.addCell(new jxl.write.Label(3, i + 1, getVal(data)));
				ws.addCell(new jxl.write.Label(4, i + 1, "P"));
				// 项目描述
				Object productType = dto.getProductType();
				String productDesc = "借款人为公司一般职员，公司成立时间长，每月收入稳定，月收入5000元以上，人行征信记录良好，此次借款主要用于偿还信用卡等短期周转";
				if (productType != null) {
					if ("2".equals(productType)) {
						productDesc = CommonProperties.getYYW_DESC_2();
					}
					if ("3".equals(productType)) {
						productDesc = CommonProperties.getYYW_DESC_3();
					}
				}
				ws.addCell(new jxl.write.Label(5, i + 1, getVal(productDesc)));
				data = dto.getCustName();
				ws.addCell(new jxl.write.Label(6, i + 1, getVal(data)));
				data =dto.getCustCardNo();
				ws.addCell(new jxl.write.Label(7, i + 1, getVal(data)));
				data = dto.getCustSex() + "";
				if (data != null) {
					if ("0".equals(data.trim())) {
						data = "女";
					}
					if ("1".equals(data.trim())) {
						data = "男";
					}
				}
				ws.addCell(new jxl.write.Label(8, i + 1, getVal(data)));
				data = dto.getCustAge();
				ws.addCell(new jxl.write.Label(9, i + 1, getVal(data)));
				data=dto.getEduction();
				ws.addCell(new jxl.write.Label(10, i + 1, getVal(data)));
				data = dto.getMarital();
				ws.addCell(new jxl.write.Label(11, i + 1, getVal(data)));
				ws.addCell(new jxl.write.Label(12, i + 1, ""));
				data = dto.getCustCardAddress();
				ws.addCell(new jxl.write.Label(13, i + 1, getVal(data)));
				ws.addCell(new jxl.write.Label(14, i + 1, random.nextInt(2) + 2 + ""));
				ws.addCell(new jxl.write.Label(15, i + 1, "良好"));
				ws.addCell(new jxl.write.Label(16, i + 1, "5000-8000"));
				ws.addCell(new jxl.write.Label(17, i + 1, ""));
				ws.addCell(new jxl.write.Label(18, i + 1, ""));
				ws.addCell(new jxl.write.Label(19, i + 1, ""));
				data = dto.getCompanyAddress();
				ws.addCell(new jxl.write.Label(20, i + 1, getVal(data)));
				ws.addCell(new jxl.write.Label(21, i + 1, random.nextInt(4) + 2 + ""));
				ws.addCell(new jxl.write.Label(22, i + 1, "批发零售或其他"));
				ws.addCell(new jxl.write.Label(23, i + 1, "私营"));
				ws.addCell(new jxl.write.Label(24, i + 1, "一般员工"));
				ws.addCell(new jxl.write.Label(25, i + 1, "E"));
				ws.addCell(new jxl.write.Label(26, i + 1, "910201230900001470"));
				ws.addCell(new jxl.write.Label(27, i + 1, "广东南粤银行"));
				ws.addCell(new jxl.write.Label(28, i + 1, "深圳"));
				ws.addCell(new jxl.write.Label(29, i + 1, "广东南粤银行深圳福田支行"));
				ws.addCell(new jxl.write.Label(30, i + 1, ""));
				ws.addCell(new jxl.write.Label(31, i + 1, ""));
				ws.addCell(new jxl.write.Label(32, i + 1, ""));
				ws.addCell(new jxl.write.Label(33, i + 1, ""));
			}
			wwb.write();
			// 关闭Excel工作薄对象
			wwb.close();
			baos.flush();
			byte[] excel = baos.toByteArray();
			baos.close();
			return excel;
		} catch (Exception e) {
			MarvelsLogUtil.getInstance().error(">>>>>>生成YYW资金Excel错误:" + e);
		}
		return null;

	}

	private byte[] writeExcelBoForMC(List<YywBatchInfoDto> dto) {
		try {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			WritableWorkbook wwb = Workbook.createWorkbook(baos);
			for (int i = 0; i < dto.size(); i++) {
				WritableSheet ws = wwb.createSheet("" + dto.get(i).getCustName(), 2);
				ws.addCell(new jxl.write.Label(0, 0, "申请期数"));
				ws.addCell(new jxl.write.Label(0, 1, "申请金额"));
				ws.addCell(new jxl.write.Label(0, 2, "审批金额 "));
				ws.addCell(new jxl.write.Label(0, 3, "借款目的"));
				ws.addCell(new jxl.write.Label(0, 4, "还款卡号"));
				ws.addCell(new jxl.write.Label(0, 5, "还款银行"));
				ws.addCell(new jxl.write.Label(0, 6, "还款银行总行号"));
				ws.addCell(new jxl.write.Label(0, 7, "客户姓名"));
				ws.addCell(new jxl.write.Label(0, 8, "身份证号"));
				ws.addCell(new jxl.write.Label(0, 9, "身份证机关"));
				ws.addCell(new jxl.write.Label(0, 10, "性别"));
				ws.addCell(new jxl.write.Label(0, 11, "年龄"));
				ws.addCell(new jxl.write.Label(0, 12, "身份证地址"));
				ws.addCell(new jxl.write.Label(0, 13, "名族"));
				ws.addCell(new jxl.write.Label(0, 14, "身份证有效开始时间"));
				ws.addCell(new jxl.write.Label(0, 15, "身份证有效结束时间"));
				ws.addCell(new jxl.write.Label(0, 16, "手机号"));
				ws.addCell(new jxl.write.Label(0, 17, "婚姻状况 "));
				ws.addCell(new jxl.write.Label(0, 18, "教育等级"));
				ws.addCell(new jxl.write.Label(0, 19, "居住地址"));
				ws.addCell(new jxl.write.Label(0, 20, "公司名称"));
				ws.addCell(new jxl.write.Label(0, 21, "公司地址"));
				ws.addCell(new jxl.write.Label(0, 22, "公司电话"));
				ws.addCell(new jxl.write.Label(0, 23, "直系亲属"));
				ws.addCell(new jxl.write.Label(0, 24, "直系亲属手机号"));

				ws.addCell(new jxl.write.Label(1, 0, getVal(dto.get(i).getApplyTerms())));
				ws.addCell(new jxl.write.Label(1, 1, getVal(dto.get(i).getAddress())));
				ws.addCell(new jxl.write.Label(1, 2, getVal(dto.get(i).getLoanMoneyAct() )));
				ws.addCell(new jxl.write.Label(1, 3, getVal(dto.get(i).getUsedName() )));
				ws.addCell(new jxl.write.Label(1, 4, getVal(dto.get(i).getRepayAcct() )));
				ws.addCell(new jxl.write.Label(1, 5, getVal(dto.get(i).getRepayBankName())));
				ws.addCell(new jxl.write.Label(1, 6, getVal(dto.get(i).getLineNumber())));
				ws.addCell(new jxl.write.Label(1, 7, getVal(dto.get(i).getCustName())));
				ws.addCell(new jxl.write.Label(1, 8, getVal(dto.get(i).getCustIdCard())));
				ws.addCell(new jxl.write.Label(1, 9, getVal(dto.get(i).getCertificateAuthority())));
				ws.addCell(new jxl.write.Label(1, 10, getVal(dto.get(i).getGender())));
				ws.addCell(new jxl.write.Label(1, 11, getVal(dto.get(i).getCustAge())));
				ws.addCell(new jxl.write.Label(1, 12, getVal(dto.get(i).getIdCardAddress())));
				ws.addCell(new jxl.write.Label(1, 13, getVal(dto.get(i).getNationality())));
				ws.addCell(new jxl.write.Label(1, 14, getVal((dto.get(i).getApplicantIdStt()).substring(0, 10))));
				ws.addCell(new jxl.write.Label(1, 15, getVal((dto.get(i).getApplicantIdExp()).substring(0, 10))));
				ws.addCell(new jxl.write.Label(1, 16, getVal(dto.get(i).getMobileNum())));
				ws.addCell(new jxl.write.Label(1, 17, getVal(dto.get(i).getMarriageName())));
				ws.addCell(new jxl.write.Label(1, 18, getVal(dto.get(i).getDescript())));
				ws.addCell(new jxl.write.Label(1, 19, getVal(dto.get(i).getAddress())));
				ws.addCell(new jxl.write.Label(1, 20, getVal(dto.get(i).getCompanyName())));
				ws.addCell(new jxl.write.Label(1, 21, getVal(dto.get(i).getCompanyAddress())));
				ws.addCell(new jxl.write.Label(1, 22, getVal(dto.get(i).getCompanyTel())));
				ws.addCell(new jxl.write.Label(1, 23, getVal(dto.get(i).getMateName())));
				ws.addCell(new jxl.write.Label(1, 24, getVal(dto.get(i).getMateMobile())));

			}
			wwb.write();
			// 关闭Excel工作薄对象
			wwb.close();
			baos.flush();
			byte[] excel = baos.toByteArray();
			baos.close();
			return excel;
		} catch (Exception e) {
			MarvelsLogUtil.getInstance().error(">>>>>>生成YYW资金Excel错误:" + e);
		}

		return null;
	}

	private String getVal(String val) {
		if (val == null || "null".equals(val)) {
			return "";
		}
		return val;
	}

	@Override
	public Map<String, Object> downList(DownListConditionDto downListInfoConditionDto) {
		Map<String, Object> result = new HashMap<>();
		List<DownListQueryDto> allDataResult = new ArrayList<DownListQueryDto>();
		String contractAccount = downListInfoConditionDto.getContractAccount();

		DownListCountAndSumDto staticData = loanDetailDao.queryCountAndSumBySearch(downListInfoConditionDto);
		BigDecimal totalMoney =new BigDecimal(staticData.getTotalMoney()) ;
		if (StringUtils.isEmpty(contractAccount) || new BigDecimal(contractAccount).compareTo(totalMoney) >= 0) {
			allDataResult = loanDetailDao.queryDetailListBySearch(downListInfoConditionDto);
		} else {
			List<DownListQueryDto> queryDetailListBySearch = loanDetailDao
					.queryDetailListBySearch(downListInfoConditionDto);
			BigDecimal tempMoney = BigDecimal.ZERO;
			for (DownListQueryDto downListQueryDto : queryDetailListBySearch) {
				tempMoney = tempMoney.add(new BigDecimal(downListQueryDto.getPrincipalAmtAct()));
				if (tempMoney.compareTo(new BigDecimal(contractAccount)) <= 0) {
					allDataResult.add(downListQueryDto);
				}
			}
		}
		totalMoney = BigDecimal.ZERO;
		String loanIds = "";
		for (DownListQueryDto dto : allDataResult) {
			BigDecimal loanMoney =new BigDecimal(dto.getPrincipalAmtAct());
			String loanId = dto.getLoanId();
			loanIds = loanIds + loanId + ",";
			totalMoney = totalMoney.add(loanMoney);
		}
		result.put("totalCount", allDataResult.size());
		result.put("totalMoney", totalMoney + "");
		if (StringUtils.isNotEmpty(loanIds)){
			result.put("loanIds", "," + loanIds.substring(0, loanIds.length()));
		}
		result.put("mpFund", downListInfoConditionDto.getFundSource());
		result.put("result", "SUCCESS");
		return result;
	}

	@Override
	public Map<String, Object> listScreenCondition() throws MarvelsBusinessException{
		Map<String, Object> map = new HashMap<>();
		List<FundSourceDTO> fundSourceList = consumerFinancialApi.fundSourceInfo(new FundSourceDTO());
		McptChannelDto mcptChannelDto=new McptChannelDto();
		CascadeChannelDto channelResult = consumProductApi.mcptChannelInfo(mcptChannelDto);
		List<McptProductType> productList = consumProductApi.mcptProductTypeInfo(new McptProductType());
		map.put("productList", productList);
		map.put("channelList", channelResult);
		map.put("fundList", fundSourceList);
		return map;
	}

	@Override
	public Map<String, Object> baseCondition() throws MarvelsBusinessException {
		Map<String, Object> map = new HashMap<>();
		List<FundSourceDTO> fundSourceList = consumerFinancialApi.fundSourceInfo(new FundSourceDTO());
		List<McptChannelDto> mcptChannelDtoList=null;
		mcptChannelDtoList = consumProductApi.mcptChannelAllInfo(new McptChannelDto());
		List<McptProductType> productList = consumProductApi.mcptProductTypeInfo(new McptProductType());
		map.put("productList", productList);
		map.put("channelList", mcptChannelDtoList);
		map.put("fundList", fundSourceList);
		return map;
	}

}
